package sandbox.psyomn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;


import Model.PeriodicTask;

/**
 * Gateway for the PeriodicTask component.
 * 
 * NOTE do not auto reformat code!!
 * 
 * NOTE we assume primary key is task name
 * 
 * Also, sqlite3 is stupid and doesn't have booleans, 
 * so if you're wondering that is why I'm using the ints
 * with the really ugly conditionals. :D! 
 * 
 * @author psyomn
 *
 */

public class PeriodicTaskGateway implements CRUD<PeriodicTask> {

	/**
	 * Set the table name to the particular model we are working on
	 */
	private String mTableName = "PeriodicTask";
	
	/** 
	 * The table specification for this object
	 */
	private String mTableSchema = new String(
			"CREATE TABLE IF NOT EXISTS " + mTableName + " ( " + 
			" taskname VARCHAR(50) PRIMARY KEY " +
			" ,taskdate DATETIME " + 
			" ,taskdone INTEGER " +  
			" ,taskfreq VARCHAR(50) " +
			" ); "
			);
	
	/**
	 * The statement string for insertions.
	 * Please note that this needs to be prepared 
	 */
	private String mInsertStatementString = new String(
			"INSERT INTO " + mTableName +
			" (taskname,taskdate,taskdone,taskfreq) " + 
			" values (?,?,?,?); "
			);
	
	/**
	 * The update statement string for updates
	 * Please note that this needs to be prepared.
	 */
	private String mUpdateStatementString = new String(
			"UPDATE " + mTableName + 
			" SET taskdate=?, taskdone=?, taskfreq=? " + 
			" WHERE taskname=?"
			);
	
	/**
	 * The statement string for deletions
	 * Please note that needs to be prepared.
	 */
	private String mDeleteStatementString = new String(
			" DELETE FROM " + mTableName +  
			" WHERE taskname=?;"
			);
	
	/**
	 * On creation, check if the table exists, if not create. 
	 */
	public PeriodicTaskGateway()
	{
		try
		{
			Statement state = Executor.getInst().getConnection().createStatement();
			state.executeUpdate(mTableSchema);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * Return all the Tasks as an array list
	 * @return an array list of single tasks
	 */
	@Override
	public ArrayList<PeriodicTask> read()
	{ 
		ArrayList<PeriodicTask> starr = new ArrayList<PeriodicTask>(); 
		ResultSet rset; 
		String db_taskname;
		String db_freq; 
		Date db_date; 
		boolean db_done; 
		
		rset = Executor.getInst().execute("SELECT * FROM " + mTableName + ";");

		/* Iterate through database elements */
		try{	
			while(rset.next())
			{
				/* Retrieve the primitives from the db */
				db_taskname = rset.getString("taskname");
				db_date = rset.getDate("taskdate"); 
				db_done = rset.getInt("taskdone") != 0 ? true : false;
				db_freq = rset.getString("taskfreq");
				
				/* Add to the array list as a task object... */
				starr.add( new PeriodicTask(
						db_taskname
						,db_date 
						,"Periodic"
				));
				
				/* ...And set its current condition */
				starr.get(starr.size()-1).setDone(db_done);
				starr.get(starr.size()-1).setRepeat(db_freq);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return starr;
	}

	/** 
	 * This should find the task in question using the primary key
	 * and update the rest of the fields 
	 */
	@Override
	public void update(PeriodicTask t)
	{
		String lookupname = t.getName(); 
		java.sql.Date d = new java.sql.Date( t.getDueDate().getTime() );
		int done = t.getDone() ? 1 : 0;
		String freq = t.getRepeat();
		PreparedStatement state; 
		Connection conn; 
		
		try
		{
			conn = Executor.getInst().getConnection(); 
			state = conn.prepareStatement(mUpdateStatementString);
			
			state.setDate(1, d);
			state.setInt(2, done);
			state.setString(3, freq);
			state.setString(4, lookupname);
			state.addBatch();
			state.executeBatch();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * For adding tasks
	 * @param t is the task to add
	 */
	@Override
	public void create(PeriodicTask t)
	{
		try
		{
			Connection conn = Executor.getInst().getConnection(); 
			PreparedStatement state = conn.prepareStatement(mInsertStatementString);
			
			System.out.println(mInsertStatementString);
			
			/* The meaty part. You're preparing, and then executing the batch. */
			state.setString(1, t.getName());
			state.setDate(2, new java.sql.Date( t.getDueDate().getTime() ));
			state.setInt(3, t.getDone() ? 1 : 0);
			state.setString(4, t.getRepeat());
			state.addBatch();
			state.executeBatch();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * Remove a task from the database 
	 * Standard sql search and destroy query
	 */
	@Override
	public void delete(PeriodicTask t)
	{
		String lookupname = t.getName();
		
		try
		{
			Connection conn = Executor.getInst().getConnection(); 
			PreparedStatement state = conn.prepareStatement(mDeleteStatementString);
			
			state.setString(1, lookupname); 
			state.addBatch();
			state.executeBatch();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}	
}
